package com.cdc.affairmanage.daoImpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import com.cdc.affairmanage.bean.Employee;
import com.cdc.affairmanage.dao.EmployeeDAO;
import com.cdc.affairmanage.util.DBConnection;
import com.cdc.affairmanage.util.Page;
public class EmployeeDAOImpl implements EmployeeDAO {
	@Override
	public List<Employee> findAllEmployee(Page page) {
		Connection conn = DBConnection.getConnection();	//获得连接对象
		String findSQL = "select * from employee " +
				"order by  joinTime desc limit ?,?";
		PreparedStatement pstmt = null;					//声明预处理对象
		ResultSet rs = null;
		List<Employee> listEmployee = new ArrayList<Employee>();
		try {
			pstmt = conn.prepareStatement(findSQL);		//获得预处理对象并赋值
			pstmt.setInt(1, page.getBeginIndex());		//查询起始点
			pstmt.setInt(2, page.getEveryPage());		//查询记录数
			rs = pstmt.executeQuery();				//执行查询
			while(rs.next()) {
				Employee employee = new Employee();
				employee.setEmployeeID(rs.getInt(1));	//设置消息ID
				employee.setEmployeeName(rs.getString(2));
				employee.setEmployeeSex(rs.getBoolean(3));
				employee.setEmployeeBirth(rs.getTimestamp(4));
				employee.setEmployeePhone(rs.getString(5));
				employee.setEmployeePlace(rs.getString(6));
				employee.setJoinTime(rs.getTimestamp(7));
				employee.setPassword(rs.getString(8));
				employee.setLead(rs.getBoolean(9));
				listEmployee.add(employee);//添加消息
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);								//关闭结果集对象
			DBConnection.close(pstmt);							//关闭预处理对象
			DBConnection.close(conn);							//关闭连接对象
		}
		return listEmployee;
	}
	
	

	public List<Employee> findAllEmployee() {
		Connection conn = DBConnection.getConnection();	//获得连接对象
		String findSQL = "select * from employee " +
				"order by  joinTime desc ";
		PreparedStatement pstmt = null;					//声明预处理对象
		ResultSet rs = null;
		List<Employee> listEmployee = new ArrayList<Employee>();
		try {
			pstmt = conn.prepareStatement(findSQL);		//获得预处理对象并赋值
			rs = pstmt.executeQuery();				//执行查询
			while(rs.next()) {
				Employee employee = new Employee();
				employee.setEmployeeID(rs.getInt(1));	//设置消息ID
				employee.setEmployeeName(rs.getString(2));
				employee.setEmployeeSex(rs.getBoolean(3));
				employee.setEmployeeBirth(rs.getTimestamp(4));
				employee.setEmployeePhone(rs.getString(5));
				employee.setEmployeePlace(rs.getString(6));
				employee.setJoinTime(rs.getTimestamp(7));
				employee.setPassword(rs.getString(8));
				employee.setLead(rs.getBoolean(9));
				listEmployee.add(employee);//添加消息
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);								//关闭结果集对象
			DBConnection.close(pstmt);							//关闭预处理对象
			DBConnection.close(conn);							//关闭连接对象
		}
		return listEmployee;
	}
	public void addEmployee(Employee employee) {
		Connection conn = DBConnection.getConnection();		//获得连接对象
		String addSQL = "insert into employee( "+
				"employeeID,employeeName,employeeSex,employeeBirth,employeePhone,employeePlace,joinTime,password,isLead) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = null;						//声明预处理对象
		try {
			pstmt = conn.prepareStatement(addSQL);			//获得预处理对象并赋值
			pstmt.setInt(1, employee.getEmployeeID());
			pstmt.setString(2, employee.getEmployeeName());	
			pstmt.setBoolean(3,employee.isEmployeeSex());
			pstmt.setTimestamp(4,new Timestamp(
					employee.getEmployeeBirth().getTime()));
			pstmt.setString(5, employee.getEmployeePhone());
			pstmt.setString(6, employee.getEmployeePlace());
			pstmt.setTimestamp(7,new Timestamp(
					employee.getJoinTime().getTime()));
			pstmt.setString(8, employee.getPassword());
			pstmt.setBoolean(9, employee.isLead());
			pstmt.executeUpdate();								//执行更新
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(pstmt);							//关闭预处理对象
			DBConnection.close(conn);							//关闭连接对象
		}
		
	}

	public void deleteEmployee(int employeeID) {
		
	}
	public Employee findEmployeeById(int employeeID) {
		Connection conn = DBConnection.getConnection();		//获得连接对象
		String findByIDSQL = "select * from " +
						"employee where employeeID = ?";	//SQL语句
		PreparedStatement pstmt = null;	//声明预处理对象
		ResultSet rs = null;
		Employee employee = null;
		try {
			pstmt = conn.prepareStatement(findByIDSQL);		//获得预处理对象并赋值
			pstmt.setInt(1, employeeID);					//设置参数
			rs = pstmt.executeQuery();						//执行查询
			if(rs.next()) {
				employee = new Employee();
				employee.setEmployeeID(rs.getInt(1));		//设置员工编号
				employee.setEmployeeName(rs.getString(2));	//设置员工姓名
				employee.setEmployeeSex(rs.getBoolean(3));	//设置员工性别
				employee.setEmployeeBirth(rs.getDate(4));	//设置出生日期
				employee.setEmployeePhone(rs.getString(5));	//设置办公室电话
				employee.setEmployeePlace(rs.getString(6));	//设置住址
				employee.setJoinTime(rs.getDate(7));		//设置录入时间
				employee.setPassword(rs.getString(8));		//设置系统口令
				employee.setLead(rs.getBoolean(9));			//设置是否为管理层领导
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);								//关闭结果集对象
			DBConnection.close(pstmt);							//关闭预处理对象
			DBConnection.close(conn);							//关闭连接对象
		}
		return employee;
	}
	public void updateEmployee(Employee employee) {
		
	}



	@Override
	public int findAllCount() {
		Connection conn = DBConnection.getConnection();	//获得连接对象
		String findSQL = "select count(*) from employee";
		PreparedStatement pstmt = null;					//声明预处理对象
		ResultSet rs = null;
		int count = 0;
		try {
			pstmt = conn.prepareStatement(findSQL);		//获得预处理对象并赋值
			rs = pstmt.executeQuery();					//执行查询
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);						//关闭结果集对象
			DBConnection.close(pstmt);					//关闭预处理对象
			DBConnection.close(conn);					//关闭连接对象
		}
		return count;
	}


	//SELECT MAX(employeeID)  FROM employee;
	@Override
	public int findMaxId() {
		// TODO Auto-generated method stub
		Connection conn = DBConnection.getConnection();	//获得连接对象
		String findSQL = "SELECT MAX(employeeID)  FROM employee";
		PreparedStatement pstmt = null;					//声明预处理对象
		ResultSet rs = null;
		int maxId = 0;
		try {
			pstmt = conn.prepareStatement(findSQL);		//获得预处理对象并赋值
			rs = pstmt.executeQuery();					//执行查询
			if(rs.next()) {
				maxId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBConnection.close(rs);						//关闭结果集对象
			DBConnection.close(pstmt);					//关闭预处理对象
			DBConnection.close(conn);					//关闭连接对象
		}
		return maxId;
	}

	
	
}
